﻿using System;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Collections.Specialized;
using System.Linq;

namespace DevSky.WebParts.DBConnectWP
{
    public partial class DLGDetailView : LayoutsPageBase
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                OpenerURL = Request.UrlReferrer.AbsoluteUri;
                UniqueDataIdentifier = Request.QueryString["id"];
                if (Request.QueryString["MODE"] != null)
                {
                    IsReadOnly = Request.QueryString["MODE"].Equals("READ");
                    if ((!IsReadOnly) && Request.QueryString["MODE"].Equals("NEW")) // Open from context menu                        
                            UniqueDataIdentifier = "-1";                    
                }
                ReadConfigFromPage();
            }
            DialogMaster dm = Master as DialogMaster;
            dm.OkButton.Click += new EventHandler(OkButton_Click);
            dm.OkButton.Enabled = !IsReadOnly;
        }

        void OkButton_Click(object sender, EventArgs e)
        {
            string status = SaveData();
            if (string.IsNullOrEmpty(status))
                Tools.Show("Data successfully saved", true);
            else
                Tools.Show(string.Format("Error during updating data:\n {0}", status), false);
        }

        private string SaveData()
        {
            string status = string.Empty;
            Control ctl = null;
            SqlCommand sqlCMD = null;
            CustomDataColumn dc = (from dataColumn in CustomDataColumns where dataColumn.IsUniqueColumn select dataColumn).FirstOrDefault();
            
            if (IsNewMode)
                sqlCMD = InsertData(ctl, dc);
            else
                sqlCMD = UpdateData(ctl, dc);

            SqlConnection cnn = new SqlConnection(CustomerConnectionString);
            int execResult = 0;
            try
            {
                cnn.Open();
                sqlCMD.Connection = cnn;
                execResult = sqlCMD.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                status = exc.Message;    
            }
            finally
            {
                cnn.Close();
            }
            return status;
        }

        //http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx
        //http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx
        private SqlCommand UpdateData(Control ctl, CustomDataColumn dc)
        {
            SqlCommand sqlCMD = new SqlCommand();
            SqlParameter param = null;
            string sqlInnerCMD = string.Format("UPDATE {0} SET [FIELDLIST] Where {1} = {2}", DBObjectName, dc.Name, UniqueDataIdentifier); // TODO CHECK DATATYPE FROM THE UNIQUE COLUMN
            StringBuilder updList = new StringBuilder();
            foreach (CustomDataColumn column in CustomDataColumns)
            {
                if (column.IsDialogVisible)
                {
                    ctl = dlgFields.FindControl(column.Name);
                    if (ctl != null)
                    {
                        param = new SqlParameter();
                        param.ParameterName = "@" + column.Name;
                        
                        if (ctl is TextBox)
                            param.Value = ((TextBox)ctl).Text;
                        if (ctl is CheckBox)
                            param.Value = ((CheckBox)ctl).Checked;
                        if (ctl is ListControl)
                            param.Value = ((ListControl)ctl).SelectedValue;
                        if (ctl is DateTimeControl)
                            param.Value = ((DateTimeControl)ctl).SelectedDate;

                        sqlCMD.Parameters.Add(param);
                        updList.AppendFormat(",{0}={1}", column.Name, param.ParameterName);
                    }
                }
            }
            sqlInnerCMD = sqlInnerCMD.Replace("[FIELDLIST]", updList.ToString().Substring(1));
            sqlCMD.CommandText = sqlInnerCMD;
            return sqlCMD;
        }

        //http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx
        private SqlCommand InsertData(Control ctl, CustomDataColumn dc)
        {
            SqlCommand sqlCMD = new SqlCommand();
            SqlParameter param = null;
            string sqlInnerCMD = string.Format("INSERT INTO {0} ([FIELDLIST]) VALUES ([VALUELIST])", DBObjectName, dc.Name, UniqueDataIdentifier); // TODO CHECK DATATYPE FROM THE UNIQUE COLUMN
            
            StringBuilder fieldList = new StringBuilder();
            StringBuilder valueList = new StringBuilder();
            foreach (CustomDataColumn column in CustomDataColumns)
            {
                if (column.IsDialogVisible)
                {
                    ctl = dlgFields.FindControl(column.Name);
                    if (ctl != null)
                    {
                        param = new SqlParameter();
                        param.ParameterName = "@" + column.Name;

                        if (ctl is TextBox)
                            param.Value = ((TextBox)ctl).Text;
                        if (ctl is CheckBox)
                            param.Value = ((CheckBox)ctl).Checked;
                        if (ctl is ListControl)
                            param.Value = ((ListControl)ctl).SelectedValue;

                        sqlCMD.Parameters.Add(param);
                        fieldList.AppendFormat(",{0}", column.Name);
                        valueList.AppendFormat(",{0}", param.ParameterName);
                    }
                }
            }
            sqlInnerCMD = sqlInnerCMD.Replace("[FIELDLIST]", fieldList.ToString().Substring(1));
            sqlInnerCMD = sqlInnerCMD.Replace("[VALUELIST]", valueList.ToString().Substring(1));
            sqlCMD.CommandText = sqlInnerCMD;
            return sqlCMD;
        }

        private string CustomerConnectionString
        {
            get
            {
                return ViewState["DLGCONNECTINFOSTRING"] as string;
            }
            set
            {
                ViewState["DLGCONNECTINFOSTRING"] = value;
            }
        }

        private string DBObjectName
        {
            get
            {
                return ViewState["DLGDBOBJECTNAME"] as string;
            }
            set
            {
                ViewState["DLGDBOBJECTNAME"] = value;
            }
        }

        private bool IsNewMode
        {
            get
            {
                return UniqueDataIdentifier.Equals("-1");
            }
        }

        private string DBObjectType
        {
            get
            {
                return ViewState["DLGDBOBJECTTYPE"] as string;
            }
            set
            {
                ViewState["DLGDBOBJECTTYPE"] = value;
            }
        }

        private string OpenerURL
        {
            get
            {
                if (ViewState["OpenerURL"] == null)
                    return string.Empty;
                return ViewState["OpenerURL"].ToString();
            }
            set
            {
                ViewState["OpenerURL"] = value;
            }
        }

        private string UniqueDataIdentifier
        {
            get
            {
                return ViewState["UniqueDataIdentifier"] as string;
            }
            set
            {
                ViewState["UniqueDataIdentifier"] = value;
            }
        }

        private bool IsReadOnly
        {
            get
            {
                if (ViewState["IsReadOnly"] != null)
                    return (bool)ViewState["IsReadOnly"];
                return true;
            }
            set
            {
                ViewState["IsReadOnly"] = value;
            }
        }

        private List<CustomDataColumn> CustomDataColumns
        {
            get
            {
                return ViewState["DATACOLUMNS"] as List<CustomDataColumn>;
            }
            set
            {
                ViewState["DATACOLUMNS"] = value;
            }
        }

        private void ReadConfigFromPage()
        {
            Configuration cfg = new Configuration(Guid.Empty, OpenerURL);
            if (cfg.ReadConfigFromPage())
            {
                CustomerConnectionString = cfg.DBConnectString;
                DBObjectName = cfg.DBObjectName;
                DBObjectType = cfg.DBObjectType;
                CustomDataColumns = cfg.CustomDataColumns;
                txtDialogTitle.Text = cfg.DialogTitle;
            }
        }

        private DataTable ReadData(string id)
        {
            DataTable table = null;
            SqlConnection cnn = new SqlConnection(CustomerConnectionString);
            try
            {                
                CustomDataColumn dc = (from dataColumn in CustomDataColumns where dataColumn.IsUniqueColumn select dataColumn).FirstOrDefault();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cnn;
                if (dc != null && DBObjectType.Equals("SP"))
                {
                    cmd.CommandText = DBObjectName;
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                if (dc != null && (DBObjectType.Equals("TABLE") || DBObjectType.Equals("VIEW")))
                {
                    cmd.CommandText = string.Format("SELECT * FROM [{0}] Where {1} = {2}", DBObjectName, dc.Name, id);
                    cmd.CommandType = CommandType.Text;
                }
                if (cmd != null)
                {
                    cnn.Open();                    
                    using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd))
                    {
                        table = new DataTable();
                        sqlAdapter.Fill(table);
                    }
                }
            }
            catch (Exception)
            {
            }
            finally
            {
                cnn.Close();
                cnn.Dispose();
            }
            return table;
        }

        private DataTable ReadLookupData(string dbSource, string keyField, string valueField)
        {
            DataTable table = null;
            SqlConnection cnn = new SqlConnection(CustomerConnectionString);
            try
            {
                cnn.Open();
                if (DBObjectType.Equals("TABLE"))
                {
                    string sqlCMD = string.Format("SELECT {0}, {1} FROM {2}", keyField, valueField, dbSource);
                    using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCMD, cnn))
                    {
                        table = new DataTable();
                        sqlAdapter.Fill(table);
                    }
                }
            }
            catch (Exception)
            {
            }
            finally
            {
                cnn.Close();
                cnn.Dispose();
            }
            return table;
        }

        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);
            ClientScriptManager csm = Page.ClientScript;
            csm.RegisterClientScriptResource(this.GetType(), "DevSky.WebParts.JavaScript.jquery-1.7.1.min.js");
            csm.RegisterClientScriptResource(this.GetType(), "DevSky.WebParts.JavaScript.jquery.maskedinput-1.3.js");            
        }

        protected override void CreateChildControls()
        {            
            base.CreateChildControls();
            RenderControls();
        }

        // http://www.aspheute.com/artikel/20000913.htm
        // http://msdn.microsoft.com/en-us/library/aa479045.aspx
        // http://www.dotnetcurry.com/ShowArticle.aspx?ID=121
        private void RenderControls()
        {
            DataTable result = ReadData(UniqueDataIdentifier);
            DataRow row = null;
            if ((result != null && result.Rows.Count > 0) || (IsNewMode))
            {
                if (result != null && result.Rows.Count > 0)
                    row = result.Rows[0];
                RequiredFieldValidator valRequired = null;
                RegularExpressionValidator regValidator = null;
                Control ctl = null;
                HtmlTable formTable = new HtmlTable();
                formTable.Attributes.Add("id", "dv-layoutTable");
                formTable.Attributes.Add("class", "dv-wpTopTable");
                formTable.Attributes.Add("cellSpacing", "0");
                formTable.Attributes.Add("cellPadding", "0");
                formTable.Attributes.Add("border", "0");
                formTable.Attributes.Add("class", "ms-formtable");
                dlgFields.Controls.Add(formTable);
                foreach (CustomDataColumn column in CustomDataColumns)
                {
                    valRequired = null;
                    regValidator = null;
                    if (column.IsDialogVisible)
                    {
                        ctl = ControlFactory(column, row);
                        if (ctl != null) // TODO: Error List
                        {
                            if (column.IsRequired && !(ctl is CheckBox) && !(ctl is DateTimeControl)) // TESTING
                            {
                                valRequired = new RequiredFieldValidator();
                                valRequired.ControlToValidate = ctl.ID;
                                valRequired.ErrorMessage = column.ErrorMessage;
                                valRequired.EnableClientScript = true;
                                valRequired.Display = ValidatorDisplay.Dynamic;
                            }
                            if ((!string.IsNullOrEmpty(column.RegularExpression)) && !(ctl is CheckBox) && !(ctl is DateTimeControl)) // TESTING
                            {
                                regValidator = new RegularExpressionValidator();
                                regValidator.EnableClientScript = true;
                                regValidator.ValidationExpression = column.RegularExpression;
                                regValidator.ErrorMessage = column.ErrorMessage;
                                regValidator.Display = ValidatorDisplay.Dynamic;
                                regValidator.ControlToValidate = ctl.ID;
                            }
                            if (column.IsRequired && ctl is DateTimeControl)
                            {
                                ((DateTimeControl)ctl).IsRequiredField = true;
                                ((DateTimeControl)ctl).ErrorMessage = column.ErrorMessage;
                            }
                            formTable.Rows.Add(BuildTableRow(column, ctl));
                            if (valRequired != null)
                            {
                                HtmlTableCell cell = new HtmlTableCell();
                                HtmlTableRow newRow = new HtmlTableRow();
                                newRow.Cells.Add(cell);
                                cell.ColSpan = 2;
                                cell.Attributes.Add("class", "errorMessage");
                                cell.Controls.Add(valRequired);
                                formTable.Rows.Add(newRow);
                            }
                            if (regValidator != null)
                            {
                                HtmlTableCell cell = new HtmlTableCell();
                                HtmlTableRow newRow = new HtmlTableRow();
                                newRow.Cells.Add(cell);
                                cell.ColSpan = 2;
                                cell.Attributes.Add("class", "errorMessage");
                                cell.Controls.Add(regValidator);
                                formTable.Rows.Add(newRow);
                            }
                        }
                    }
                }
            }
            else
            {
                Tools.Show("Unable to load detail data.\nPlease verify that you have specified the correct unique data column.", true);
            }
        }

        private Control ControlFactory(CustomDataColumn column, DataRow row)
        {
            Control ctl = null;
            string ctlValue = string.Empty;

            if (row != null)
                ctlValue = row[column.Name]!=null?row[column.Name].ToString():string.Empty;

            if (column.IsLookupColumn)
            {
                DataTable values = ReadLookupData(column.Lookup.DBObjectName, column.Lookup.KeyField, column.Lookup.DisplayField);
                ListControl ddList = null;

                if (column.Lookup.ShowListAs.Equals("DROPDOWN") || column.Lookup.ShowListAs.Equals("NONE"))
                    ddList = new DropDownList();
                if (column.Lookup.ShowListAs.Equals("RADIOBUTTON"))
                    ddList = new RadioButtonList();
                if (column.Lookup.ShowListAs.Equals("CHECKBOX"))
                    ddList = new CheckBoxList();
                
                if (values != null)
                {
                    ddList.DataSource = values;
                    ddList.DataValueField = column.Lookup.KeyField;
                    ddList.DataTextField = column.Lookup.DisplayField;
                    ddList.SelectedValue = ctlValue;
                    ddList.DataBind();
                }
                ctl = ddList;
            }
            else
            {
                if (DBTypes.IsBoolean(column))
                    ctl = new CheckBox() { Checked = ctlValue.ToUpper().Equals("TRUE") };

                if (DBTypes.IsDateTime(column))
                {
                    DateTime currentDate = DateTime.MinValue;
                    if (!string.IsNullOrEmpty(ctlValue) && DateTime.TryParse(ctlValue, out currentDate))
                        ctl = new DateTimeControl() { SelectedDate = currentDate, DateOnly=true };
                    else
                        ctl = new DateTimeControl();
                }
                if (DBTypes.IsText(column))
                    ctl = new TextBox() { Text = ctlValue, TextMode = TextBoxMode.SingleLine };
                if (DBTypes.IsInteger(column))
                {
                    int currentInt = 0;
                    if (Int32.TryParse(ctlValue, out currentInt))
                        ctl = new TextBox() { Text = currentInt.ToString() };
                    else
                        ctl = new TextBox();
                }
                if (DBTypes.IsDecimal(column))
                {
                    float currentFloat = 0;
                    if (float.TryParse(ctlValue, out currentFloat))
                        ctl = new TextBox() { Text = currentFloat.ToString() };
                    else
                        ctl = new TextBox();
                }
                if (DBTypes.IsMoney(column))
                {
                    decimal currentDecimal = 0;
                    if (decimal.TryParse(ctlValue, out currentDecimal))
                        ctl = new TextBox() { Text = currentDecimal.ToString() };
                    else
                        ctl = new TextBox();
                }
            }
            if (ctl != null)
            {
                ctl.ID = column.Name;
                if (!string.IsNullOrEmpty(column.InputFormat) && ctl is WebControl)
                    ((WebControl)ctl).Attributes.Add("devskycstformat", column.InputFormat);
                if (!string.IsNullOrEmpty(column.Tooltip) && ctl is WebControl)
                    ((WebControl)ctl).Attributes.Add("title", column.Tooltip);
                if (ctl is WebControl)
                    ((WebControl)ctl).Enabled = !IsReadOnly;
                if (ctl is DateTimeControl)
                    ((DateTimeControl)ctl).Enabled = !IsReadOnly;
            }
            return ctl;
        }

        private HtmlTableRow BuildTableRow(CustomDataColumn column, Control input)
        {
            HtmlTableRow row = new HtmlTableRow();
            HtmlTableCell cell = new HtmlTableCell();
            cell.Attributes.Add("valign", "top");
            cell.Attributes.Add("class", "dv-formlabel");
            HtmlGenericControl header3 = new HtmlGenericControl("h3");
            header3.Attributes.Add("class", "dv-standardheader");
            HtmlGenericControl nobr = new HtmlGenericControl("nobr");
            nobr.InnerText = string.IsNullOrEmpty(column.DisplayName) ? column.Name : column.DisplayName;
            if (column.IsRequired)
            {
                HtmlGenericControl required = new HtmlGenericControl("span") { InnerText = " *" };
                required.Attributes.Add("class", "dv-formvalidation");
                nobr.Controls.Add(required);
            }
            header3.Controls.Add(nobr);            
            cell.Controls.Add(header3);
            row.Cells.Add(cell);
            cell = new HtmlTableCell();
            cell.Attributes.Add("valign", "top");
            cell.Attributes.Add("class", "dv-formbody");
            HtmlGenericControl span = new HtmlGenericControl("span");
            span.Attributes.Add("dir", "none");
            if (DBTypes.IsText(column) && input is WebControl)
                ((WebControl)input).Attributes.Add("class", "dv-long");
            span.Controls.Add(input);
            cell.Controls.Add(span);
            row.Cells.Add(cell);
            return row;
        }
    }
}
